package com.lill.system.serivce.impl;

import java.io.InputStream;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.collections.MapUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.commons.lang.time.DateFormatUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.lill.common.component.util.DateUtils;
import com.lill.system.dao.ISysImportDao;
import com.lill.system.service.ISysImportService;

@Service("com.lill.system.serivce.impl.SysImportServiceImpl")
public class SysImportServiceImpl implements ISysImportService {
	
	protected Logger logger = LoggerFactory.getLogger(getClass());
	
	@Autowired
	private ISysImportDao sysImportDao;

	public List<Map<String, Object>> readDataListFromExcel(int rowNumStart,String booktable,InputStream in, boolean xlsxFormat) {
		List<Map<String, Object>> importDataList=null;
		Workbook wb = null;
		try {
			Map<String, Object> baseMap=sysImportDao.queryExcelBase(booktable);
			if(MapUtils.isNotEmpty(baseMap)){
				int start=Integer.parseInt(baseMap.get("num").toString());
				rowNumStart=start;
				List<Map<String, Object>> formatMapList=sysImportDao.queryExcelFormat(booktable);
				if(CollectionUtils.isNotEmpty(formatMapList)){
					if(xlsxFormat){
						wb = new XSSFWorkbook(in);
					}else{
						wb = new HSSFWorkbook(in);
					}
					Sheet sheetData=wb.getSheetAt(0);
					int maxRow=sheetData.getLastRowNum();
					if(maxRow>=start){
						importDataList=new ArrayList<Map<String,Object>>(maxRow-start);
						for (int i = start; i <= maxRow; i++) {
							Map<String, Object> dataMap=new HashMap<String, Object>(formatMapList.size());
							Row rowData=sheetData.getRow(i);
							if(null!=rowData){
								for (Map<String, Object> formatMap : formatMapList) {
									int colNum=Integer.parseInt(formatMap.get("num").toString());
									Cell cell=rowData.getCell(colNum);
									String colVal="0";
									if(null!=cell){
										CellType cellType = cell.getCellTypeEnum();
										switch (cellType) {
										case NUMERIC:
											if(HSSFDateUtil.isCellDateFormatted(cell)){
												Date date = cell.getDateCellValue();
												colVal = DateFormatUtils.format(date, "yyyy-MM-dd");
											}else{
												BigDecimal bdVal = new BigDecimal(Double.toString(cell.getNumericCellValue()));
												colVal=bdVal.toPlainString().replaceAll("0+?$", "").replaceAll("[.]$", "");
											}
											break;
										case FORMULA:
							                try {
							                	colVal = cell.getStringCellValue();
							                } catch (IllegalStateException e) {
							                	try {
													colVal = String.valueOf(cell.getNumericCellValue());
												} catch (Exception e1) {
													colVal="0";
												}
							                }
							                break;
										default:colVal=cell.toString();break;
										}
									}
									if(StringUtils.isEmpty(colVal.trim())){
										colVal="0";
									}
									if(formatMap.get("colname").toString().contains("_date")&&"0".equals(colVal)){
										colVal=DateUtils.format(new Date());
									}
									dataMap.put(formatMap.get("colname").toString(), colVal);
								}
							}
							importDataList.add(dataMap);
						}
					}
				}
			}
		} catch (Exception e) {
			logger.error(e.getMessage(), e);
		} finally {
			try {
				if(null!=wb){
					wb.close();
				}
			} catch (Exception e) {
			}
			try {
				if(null!=in){
					in.close();
				}
			} catch (Exception e) {
			}
		}
		return importDataList;
	}

}
